import pymysql
import datetime
import time
import redis
import jwt

def get_redis_db():
    db=redis.Redis(host="192.168.157.100",port=6379,password="20010903",decode_responses=False)
    return db

def get_db():
    db = pymysql.connect(host='wlff.site', user='remoteroot', port=3307, password='213471284',database='Book_management')
    return db
def create_jwt(name,password):
    db=get_db()
    try:
        cursor=db.cursor()
        cursor.execute("SELECT `id` FROM user where account='%s' and password='%s'" % (name, password))
        data = cursor.fetchone()
        if data is None:
            raise Exception("用户不存在")
    except Exception as e:
        db.close()
        return {
            'stat':False,
            'text':str(e)
        }
    db.close()
    timeout=datetime.timedelta(seconds=100)
    exp=datetime.datetime.utcnow()+timeout
    headers={
        "alg": "HS256",
        "typ": "JWT"
    }
    salt="d1wVe6dvEFbWEef0nygWrrb"
    payload={
        'id':data[0],
        'exp':exp
    }
    token = jwt.encode(payload,salt,headers=headers)
    return {
        'stat': True,
        'text': token
    }
def decode_jwt(token):
    salt = "d1wVe6dvEFbWEef0nygWrrb"
    try:
        info=jwt.decode(jwt=token,key=salt,algorithms='HS256')
    except Exception as e:
        return {
            'stat':False,
            'text':str(e)
        }
    return {
            'stat':True,
            'text':info
        }

def createUser(name,password):
    db=get_db()
    cursor = db.cursor()
    try:
        print("创建用户：%s" % (name))
        cursor.execute("SELECT count(*) FROM user where account='%s' and password='%s'" % (name, password))
        data = cursor.fetchone()
        if data[0] > 0:
            raise Exception("用户已存在")
        cursor.execute("INSERT INTO `user` (account,password) VALUES('%s','%s')" % (name,password))
        db.commit()
    except Exception as e:
        db.close()
        return str(e)
    db.close()
    return "success"
def login(name,password):
    return create_jwt(name,password)

def borrow(bookId,num,name,password):
    db=get_db()
    cursor = db.cursor()
    current_time = time.time()
    now = datetime.datetime.utcfromtimestamp(current_time).strftime("%Y-%m-%d %H:%M:%S")
    try:
        cursor.execute("SELECT count(*) FROM user where account='%s' and password='%s'" % (name,password))
        data = cursor.fetchone()
        if data[0]==0 :
            raise Exception("用户名或密码输入错误")
        cursor.execute("SELECT id FROM user where account='%s' and password='%s'" % (name, password))
        userid = cursor.fetchone()
        cursor.execute("SELECT count(*) FROM book_borrow where book_id=%s and user_id=%s and revert_book=0" % (bookId,userid[0]))#该用户借过该书后未归还该书无法借该书
        revert = cursor.fetchone()
        if revert[0]!=0:
            raise Exception("已借阅该书籍")
        cursor.execute("SELECT book_surplus FROM book_info where book_id=%s" % (bookId))
        data = cursor.fetchone()#可借阅最大数量
        cursor.execute("UPDATE book_info SET book_surplus=%s where book_id=%s" % (data[0]-num,bookId))#可借阅-num
        if data[0] <=0 :
            raise Exception("书籍已无法借阅")
        if data[0]-num <0 :
            raise Exception("书籍借阅失败")
        cursor.execute("INSERT INTO `book_borrow` (book_id,borrow_num,user_id,borrow_time) VALUES('%s','%s','%s','%s')" % (bookId,num,userid[0],now))
        cursor.execute("INSERT INTO `book_borrow_history` (book_id,borrow_num,user_id,borrow_time) VALUES('%s','%s','%s','%s')" % (bookId, num, userid[0], now))
        db.commit()
    except Exception as e:
        db.rollback()
        db.close()
        return str(e)
    db.close()
    return "success"
# data = cursor.fetchone()
# print(data)
def returnBooks(bookId,num,name,password):
    db=get_db()
    cursor = db.cursor()
    try:
        cursor.execute("SELECT count(*) FROM user where account='%s' and password='%s'" % (name,password))
        data = cursor.fetchone()
        if data[0]==0 :
            raise Exception("用户名或密码输入错误")
        cursor.execute("SELECT id FROM user where account='%s' and password='%s'" % (name, password))
        userid = cursor.fetchone()

        cursor.execute("SELECT book_surplus FROM book_info where book_id=%s" % (bookId))#查询该书可借阅的值
        book = cursor.fetchone()
        cursor.execute("SELECT borrow_num FROM book_borrow where book_id=%s and user_id=%s and revert_book=0" % (bookId,userid[0]))#查询该用户之前借了该书多少本
        bookBorrow=cursor.fetchone()
        cursor.execute("UPDATE book_info SET book_surplus=%s where book_id=%s" % (book[0] + num, bookId))  # 可借阅+num
        if num<bookBorrow[0] :
            print("UPDATE book_borrow SET borrow_num=%s where user_id=%s" % (bookBorrow[0]-num,userid[0]))
            cursor.execute("UPDATE book_borrow SET borrow_num=%s where user_id=%s and revert_book=0" % (bookBorrow[0]-num,userid[0]))#给用户修改剩余还书数量
        elif num==bookBorrow[0] :
            cursor.execute("UPDATE book_borrow SET revert_book=1 where user_id=%s and book_id=%s and revert_book=0" % (userid[0],bookId))#给用户修改已还完
            cursor.execute("UPDATE book_borrow_history SET revert_book=1 where user_id=%s and book_id=%s and revert_book=0" % (userid[0], bookId))#增加历史还完
        elif num>bookBorrow[0]:
            raise Exception("还书超出数量")
        db.commit()
    except Exception as e:
        db.rollback()
        db.close()
        return str(e)

    db.close()
    return "success"
returnBooks(4,2,'ccc','ccc')
def selectUser():
    redisdb=get_redis_db()
    redisresult=redisdb.get('selectuser')
    if redisresult is None :
        db = get_db()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM book_info")
        data = cursor.fetchall()
        print('从数据库获取：' + str(data))
        redisdb.set('selectuser', str(data))#插入redis数据库
        redisdb.expire('selectuser',10)
    else:
        redisresult=redisresult.decode()
        print('从redis获取：'+str(redisresult))
        data=redisresult


    return data

# selectUser()
# token=create_jwt('ccc','ccc')
# print(token)
# print(decode_jwt(token))

# print(decode_jwt('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6NywiZXhwIjoxNzE4NjgwNjUyfQ.E4PzHc_RWCz4yuQG8xb7epFhnnMc9Q8KJVGbUINV3hY'))